# Copyright (c) 2025, Your Company Pvt. Ltd.
# License: GNU General Public License v3. See license.txt

from datetime import datetime
import frappe
from frappe import _
from frappe.utils import cint, flt, getdate, add_days

Filters = frappe._dict


def execute(filters: Filters = None) -> tuple:
    # 计算包含今天在内的7天日期范围：today-6天 到 today（共7天）
    today = getdate()
    start_date = add_days(today, -6)  # 从今天往前推6天（包含今天共7天）
    end_date = today  # 结束日期为今天
    filters["start_date"] = start_date
    filters["end_date"] = end_date

    # 动态生成列（物料基础列 + 7天号数列 + 总用量列）
    columns = get_columns(start_date, end_date)
    
    # 获取原生SQL查询的使用量数据
    usage_data = get_daily_usage_data(filters)
    
    # 格式化数据（确保每个物料7天数据完整，无用量显示0）
    data = format_report_data(filters, usage_data, start_date, end_date)
    
    # 图表数据返回空（隐藏图表）
    chart_data = {}
    
    return columns, data, None, chart_data


def get_daily_usage_data(filters: Filters) -> dict:
    """原生SQL查询：仅统计“调入HX01-005/HX02-003仓库”的每日使用量"""
    # 提取筛选参数
    start_date = filters["start_date"]
    end_date = filters["end_date"]
    company = filters.get("company")
    item_code = filters.get("item_code")
    item_group = filters.get("item_group")
    show_disabled = 1 if filters.get("show_disabled_items") else 0

    # 目标仓库列表
    target_warehouses = ("HX01-005", "HX02-003")
    sql = """
        SELECT
            item.name AS item_code,
            item.item_name AS item_name,
            item.item_group AS item_group,
            item.stock_uom AS uom,
            se.posting_date AS usage_date,
            SUM(IFNULL(sed.qty, 0)) AS daily_qty
        FROM
            `tabStock Entry` se
        INNER JOIN `tabStock Entry Detail` sed 
            ON se.name = sed.parent
        INNER JOIN `tabItem` item 
            ON sed.item_code = item.name
        WHERE
            se.stock_entry_type = 'Material Transfer'
            AND se.posting_date BETWEEN %s AND %s
            AND se.docstatus != 2
            AND item.disabled = %s
            AND sed.t_warehouse IN %s
            AND sed.s_warehouse NOT IN %s
    """

    params = [
        start_date,
        end_date,
        show_disabled,
        target_warehouses,
        target_warehouses
    ]

    # 动态添加筛选条件
    if company:
        sql += " AND se.company = %s"
        params.append(company)
    if item_code:
        sql += " AND item.name = %s"
        params.append(item_code)
    if item_group:
        sql += " AND item.item_group = %s"
        params.append(item_group)

    sql += """
        GROUP BY
            item.name, item.item_name, item.item_group, item.stock_uom, se.posting_date
        ORDER BY
            item.name, se.posting_date
    """

    usage_dict = {}
    for row in frappe.db.sql(sql, params, as_dict=True):
        usage_date = row["usage_date"] if isinstance(row["usage_date"], datetime) else getdate(row["usage_date"])
        key = (row["item_code"], usage_date)
        usage_dict[key] = flt(row["daily_qty"], 2)

    return usage_dict


def format_report_data(filters: Filters, usage_dict: dict, start_date: datetime, end_date: datetime) -> list[dict]:
    """格式化报表数据：每个物料7天数据完整，无用量显示0"""
    data = []
    precision = cint(frappe.db.get_single_value("System Settings", "float_precision")) or 2

    item_filters = {
        "disabled": 0 if not filters.get("show_disabled_items") else 1,
        "item_group": ["in", ["过沥架", "电镀槽", "钝化",'封闭','前处理']]
    }
    if filters.get("item_code"):
        item_filters["name"] = filters["item_code"]
    if filters.get("item_group"):
        item_filters["item_group"] = filters["item_group"]

    items = frappe.get_all(
        "Item",
        filters=item_filters,
        fields=["name", "item_name", "item_group", "stock_uom"],
        order_by="name"
    )

    # 生成包含今天在内的7天日期列表（从start_date到end_date）
    date_list = [add_days(start_date, i) for i in range(7)]

    for item in items:
        row = {
            "item_code": item["name"],
            "item_name": item["item_name"],
            "item_group": item["item_group"],
            "uom": item["stock_uom"]
        }

        total_7days = 0.0
        for date in date_list:
            date_key = date.strftime("%d")
            usage_key = (item["name"], date)
            daily_qty = flt(usage_dict.get(usage_key, 0.0), precision)
            row[date_key] = daily_qty
            total_7days += daily_qty

        row["total_7days_usage"] = flt(total_7days, precision)
        data.append(row)

    return data


def get_columns(start_date: datetime, end_date: datetime) -> list[dict]:
    """动态生成报表列：物料基础列 + 7天号数列 + 总用量列"""
    columns = [
        {
            "label": _("物料编码"),
            "fieldname": "item_code",
            "fieldtype": "Link",
            "options": "Item",
            "width": 120
        },
        {
            "label": _("物料名称"),
            "fieldname": "item_name",
            "fieldtype": "Data",
            "width": 180
        },
        {
            "label": _("物料组"),
            "fieldname": "item_group",
            "fieldtype": "Link",
            "options": "Item Group",
            "width": 120
        },
    ]

    # 生成包含今天在内的7天日期列表
    date_list = [add_days(start_date, i) for i in range(7)]
    for date in date_list:
        date_str = date.strftime("%d")
        columns.append({
            "label": _(date_str),
            "fieldname": date_str,
            "fieldtype": "Float",
            "width": 80,
            "precision": 2
        })

    columns.extend([
        {
            "label": _("7天总用量"),
            "fieldname": "total_7days_usage",
            "fieldtype": "Float",
            "width": 100,
            "precision": 2,
            "bold": True
        },
        {
            "label": _("单位"),
            "fieldname": "uom",
            "fieldtype": "Link",
            "options": "UOM",
            "width": 80
        }
    ])

    return columns


def get_chart_data(data: list, filters: Filters, start_date: datetime, end_date: datetime) -> dict:
    """隐藏图表，返回空数据"""
    return {}